package com.ttctracker.util;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SqlOpenHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "ttc_bus_tracker";
	private static final int DATABASE_VERSION = 1;

	public SqlOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);

	}
	
	protected static String getDatabaseName() {
		return DATABASE_NAME;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.execSQL("CREATE TABLE route (id INTEGER primary key autoincrement, route_tag text UNIQUE, route_title DEFAULT '')");
			db.execSQL("CREATE TABLE direction(id INTEGER PRIMARY KEY autoincrement, dir_tag text DEFAULT '', dir_title text DEFAULT '', dir_name text DEFAULT '', dir_stops text DEFAULT '', route_tag text, FOREIGN key(route_tag) REFERENCES route(route_tag))");
			db.execSQL("CREATE TABLE stops(id INTEGER PRIMARY KEY autoincrement, stop_tag text, stop_title text, stop_lat REAL, stop_lon REAL, stop_id text DEFAULT '', route_tag text, FOREIGN key(route_tag) REFERENCES route(route_tag))");
			//db.execSQL("CREATE TABLE dir_stops(dir_tag text not null REFERENCES direction(dir_tag), stop_tag text not null REFERENCES stops(stop_tag), route_tag text not null REFERENCES route(route_tag), PRIMARY KEY (dir_tag, stop_tag))");
			db.execSQL("CREATE TABLE user_stops(id INTEGER PRIMARY KEY autoincrement, stop_tag text REFERENCES stops(stop_tag), s_id INTEGER UNIQUE REFERENCES stops(id), added_date integer default (strftime('%s','now')))");

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(SqlOpenHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS route");
		db.execSQL("DROP TABLE IF EXISTS direction");
		db.execSQL("DROP TABLE IF EXISTS stops");
		//db.execSQL("DROP TABLE IF EXISTS dir_stops");
		db.execSQL("DROP TABLE IF EXISTS user_stops");

		onCreate(db);
	}

}
